package com.minstone.common.util;

import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

public class SqlUtil {
	private static final Logger LOGGER = Logger.getLogger(SqlUtil.class);

	// MD5编码

	public static String EncoderByMd5(String str) {
		// 确定计算方法
		// MessageDigest md5;
		//
		// String newstr = "";
		// Base64 base64en = new Base64();
		// try {
		// md5 = MessageDigest.getInstance("MD5");
		// newstr = new String(base64en.encode(md5.digest(str
		// .getBytes("utf-8"))));
		//
		//
		byte[] md5 = null;
		try {

			MessageDigest md = MessageDigest.getInstance("MD5");

			md5 = md.digest(str.getBytes());

		} catch (Exception e) {

			LOGGER.error(e.getMessage(), e);

		}
		// } catch (Exception e) {
		// LOGGER.error(e.getMessage(), e);
		// }
		// 加密后的字符串

		return bytetoString(md5);
	}

	public static String bytetoString(byte[] digest) {
		String str = "";
		String tempStr = "";
		for (int i = 0; i < digest.length; i++) {
			tempStr = (Integer.toHexString(digest[i] & 0xff));
			if (tempStr.length() == 1) {
				str = str + "0" + tempStr;
			} else {
				str = str + tempStr;
			}
		}
		return str.toLowerCase();

	}

	/**
	 * �߼�������� // ����ʱ��Ĳ��� // APPR_DATE*greate*Date ��ʾ APPR_DATE
	 * Ϊ�������� �Ҹ�����Ϊ���� ͬ��lessΪС�� // APPR_DATE*less //APPR_DATE*equal //
	 * APPR_DATE*like //REG_CAPITAL*greate*Integer ��ʾ REG_CAPITAL
	 * Ϊ��������Ϊ���� //AREA_NAME!in!String ��ʾAREA_NAME ��value�ķ�Χ��
	 * 
	 * */
	public static String comParseParam(Map data) {
		List temp = new ArrayList();
		StringBuffer buffer = new StringBuffer();
		Iterator ite = data.keySet().iterator();
		while (ite.hasNext()) {
			String key = (String) ite.next();
			if (key.lastIndexOf("*") > 0) {// ʱ������
				String[] tem = key.split("\\*");
				String columnet = tem[0];
				String dataType = tem[2];
				String oper = tem[1];
				String value = data.get(key) == null ? "" : data.get(key)
						.toString();
				value = value.trim();
				if (StringUtils.isBlank(value)) {
					continue;

				}
				if (oper.equals("less")) {

					buffer.append(getValue(columnet, " < ", value, dataType));

				}

				if (oper.equals("greate")) {

					buffer.append(getValue(columnet, " > ", value, dataType));
				}

				if (oper.equals("equal")) {

					buffer.append(getValue(columnet, " = ", value, dataType));
				}

				if (oper.equals("like")) {

					buffer.append(getValue(columnet, " like ", "%" + value
							+ "%", dataType));

				}
				continue;

			}

			if (key.lastIndexOf("!") > 0) {// in ��Χ

				String[] t = key.split("!");
				String columen = t[0];
				String oper = t[1];
				String dataType = t[2];
				String value = (String) data.get(key);
				value = value.trim();
				if (value == null || "".equals(value)) {
					continue;

				}
				if (value.lastIndexOf(",") < 0) {
					buffer.append(" and " + columen).append(" " + oper)
							.append(strToQuery(value));

				} else {
					buffer.append(" and " + columen).append(" " + oper)
							.append("(" + strToQuery(value) + ")");

				}

			}

		}

		return buffer.toString();

	}

	/**
	 * ����insert���
	 * 
	 * @param ob
	 *            bean
	 * @param tableName
	 *            Ҫ����ı���
	 * @param param
	 *            ����
	 * @author zouyong
	 * */
	public static String buildInsertSql(Object ob, String tableName) {
		if (ob == null || "".equals(ob)) {
			return "";

		}
		if (tableName == null || "".equals(tableName)) {
			tableName = "?";

		}
		Class tempClass = ob.getClass();
		Field[] fields = tempClass.getDeclaredFields();
		StringBuffer buffer = new StringBuffer();
		StringBuffer field = new StringBuffer();
		for (int i = 0; i < fields.length; i++) {
			Field fiel = (Field) fields[i];
			field.append(fiel.getName());
			Object value = CommonBeanUtils.getter(ob, fiel.getName());
			if (value == null) {
				value = "null";

			}
			if (value instanceof String) {
				if (value.equals("null")) {
					buffer.append(value);

				} else {
					buffer.append("'");
					buffer.append(value);
					buffer.append("'");
				}
			}

			if (value instanceof Boolean) {
				boolean s = Boolean.getBoolean(value.toString());
				if (s) {
					buffer.append(1);

				} else {
					buffer.append(0);
				}

			}
			if (value instanceof Float) {
				buffer.append(value);

			}

			if (value instanceof Integer) {

				buffer.append(value);

			}

			if (value instanceof Timestamp) {
				buffer.append("to_date('"
						+ value.toString().substring(0,
								value.toString().lastIndexOf("."))
						+ "','yyyy-MM-dd hh24:mi:ss')");
			} else if (value instanceof Date) {
				// SimpleDateFormat format = new
				// SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String temp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value);
				buffer.append("to_date('" + temp + "','yyyy-MM-dd hh24:mi:ss')");
			}
			field.append(",");
			buffer.append(",");
		}
		String temp = buffer.substring(0, buffer.lastIndexOf(","));
		String temp1 = field.substring(0, field.lastIndexOf(","));
		String sql = "insert into " + tableName + " (" + temp1 + ") values("
				+ temp + ")";

		return sql;

	}

	private static String strToQuery(String s) {
		StringBuffer temp = new StringBuffer();
		String[] arg = s.split(",");
		for (int i = 0; i < arg.length; i++) {
			String t = (String) arg[i];
			t = t.trim();
			temp.append("'" + t + "'");
			temp.append(",");

		}
		String s1 = temp.toString();
		if (s1.lastIndexOf(",") > 0) {
			return s1.substring(0, s1.lastIndexOf(","));

		}
		return "'error'";
	}

	private static String getValue(String colum, String ope, String value,
			String dateType) {
		StringBuffer buffer = new StringBuffer();
		dateType = dateType.toLowerCase();
		if (dateType.equals("date")) {

			buffer.append(" and " + colum).append(ope)
					.append(SqlUtil.to_char_date(value));
		}
		if (dateType.equals("string")) {

			buffer.append(" and " + colum).append(ope)
					.append("'" + value + "'");

		}
		if (dateType.equals("integer")) {
			buffer.append(" and " + colum).append(ope).append(value);

		}

		return buffer.toString();
	}

	public static String to_char(Date date) {
		String str = new SimpleDateFormat("yyyy-MM-dd").format(date);
		String sql = "to_date('" + str + "','yyyy-MM-dd hh24:mi:ss')";
		return sql;

	}

	public static String to_char_date(Date date) {
		String str = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
		String sql = "to_date('" + str + "','yyyy-MM-dd')";
		return sql;

	}

	public static String to_char(String str) {
		String sql = "to_date('" + str + "','yyyy-MM-dd hh24:mi:ss')";
		return sql;

	}

	public static String to_char_date(String str) {
		String sql = "to_date('" + str + "','yyyy-MM-dd')";
		return sql;

	}

	public static String buildUpdateSql(Object ob, String tableName, Map param,
			Map notUpdate) {
		if (ob == null || "".equals(ob) || param.size() == 0) {
			return "";

		}
		if (tableName == null || "".equals(tableName)) {
			tableName = "?";

		}
		Class tempClass = ob.getClass();
		Field[] fields = tempClass.getDeclaredFields();
		StringBuffer buffer = new StringBuffer();
		StringBuffer field = new StringBuffer();
		for (int i = 0; i < fields.length; i++) {
			Field fiel = fields[i];
			if (notUpdate.containsKey(fiel.getName())) {
				continue;

			}
			field.append(fiel.getName());
			Object value = CommonBeanUtils.getter(ob, fiel.getName());

			if (value == null || value.equals("null")) {
				value = "null";
				continue;
			}
			if (value instanceof String) {
				if (value.equals("null")) {
					buffer.append(fiel.getName() + "=" + value);

				} else {

					buffer.append(fiel.getName() + "=" + "'" + value + "'");
				}
			}
			if (value instanceof Float) {
				buffer.append(fiel.getName() + "=" + value);

			}
			if (value instanceof Long) {
				buffer.append(fiel.getName() + "=" + value);

			}
			if (value instanceof Integer) {
				buffer.append(fiel.getName() + "=" + value);
			}

			if (value instanceof Boolean) {
				boolean temp = Boolean.getBoolean(value.toString());
				if (temp) {
					buffer.append(fiel.getName() + "=1");

				} else {
					buffer.append(fiel.getName() + "=0");
				}

			}
			if (value instanceof Timestamp) {
				buffer.append(fiel.getName()
						+ "=to_date('"
						+ value.toString().substring(0,
								value.toString().lastIndexOf("."))
						+ "','yyyy-MM-dd hh24:mi:ss')");
			} else if (value instanceof Date) {
				SimpleDateFormat format = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");
				String temp = format.format(value);
				buffer.append(fiel.getName() + "=to_date('" + temp
						+ "','yyyy-MM-dd hh24:mi:ss')");
			}
			field.append(",");
			buffer.append(",");
		}
		String temp = buffer.substring(0, buffer.lastIndexOf(","));
		String temp1 = field.substring(0, field.lastIndexOf(","));

		String sql = "update " + tableName + " set " + temp + " where 1=1"
				+ parseParam(param);

		return sql;

	}

	public static String buildUpdateSql(Object ob, String tableName, Map param) {
		if (ob == null || "".equals(ob) || param.size() == 0) {
			return "";

		}
		if (tableName == null || "".equals(tableName)) {
			tableName = "?";

		}
		Class tempClass = ob.getClass();
		Field[] fields = tempClass.getDeclaredFields();
		StringBuffer buffer = new StringBuffer();
		StringBuffer field = new StringBuffer();
		for (int i = 0; i < fields.length; i++) {
			Field fiel = fields[i];
			field.append(fiel.getName());
			Object value = CommonBeanUtils.getter(ob, fiel.getName());

			if (value == null || value.equals("null")) {
				value = "null";
				continue;
			}
			if (value instanceof String) {
				if (value.equals("null")) {
					buffer.append(fiel.getName() + "=" + value);

				} else {

					buffer.append(fiel.getName() + "=" + "'" + value + "'");
				}
			}
			if (value instanceof Float) {
				buffer.append(fiel.getName() + "=" + value);

			}
			if (value instanceof Integer) {

				if (value.toString().equals("-9")) {
					continue;
				}
				buffer.append(fiel.getName() + "=" + value);

			}

			if (value instanceof Boolean) {
				boolean temp = Boolean.getBoolean(value.toString());
				if (temp) {
					buffer.append(fiel.getName() + "=1");

				} else {
					buffer.append(fiel.getName() + "=0");
				}

			}
			if (value instanceof Timestamp) {
				buffer.append(fiel.getName()
						+ "=to_date('"
						+ value.toString().substring(0,
								value.toString().lastIndexOf("."))
						+ "','yyyy-MM-dd hh24:mi:ss')");
			} else if (value instanceof Date) {
				SimpleDateFormat format = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");
				String temp = format.format(value);
				buffer.append(fiel.getName() + "=to_date('" + temp
						+ "','yyyy-MM-dd hh24:mi:ss')");
			}
			field.append(",");
			buffer.append(",");
		}
		String temp = buffer.substring(0, buffer.lastIndexOf(","));
		// String temp1 = field.substring(0, field.lastIndexOf(","));

		String sql = "update " + tableName + " set " + temp + " where 1=1"
				+ parseParam(param);

		return sql;

	}

	public static String parseParam(Map param) {
		Set key = param.keySet();
		StringBuffer buffer = new StringBuffer();
		Iterator iterator = key.iterator();
		while (iterator.hasNext()) {

			String keys = (String) iterator.next();
			if (param.get(keys) == null || "".equals(param.get(keys))) {

				continue;
			}
			buffer.append(" and " + keys + " = '" + param.get(keys) + "'");
		}
		String temp = "";
		try {
			temp = buffer.toString();
		} catch (Exception e) {
			LOGGER.error(e.getMessage(), e);
			return "";

		}
		return temp;

	}

	/**
	 * ����insert���
	 * 
	 * @param ob
	 *            bean
	 * @param tableName
	 *            Ҫ����ı���
	 * @param param
	 *            ����
	 * @author zouyong
	 * */

	/**
	 * MD5���ID
	 * */

	public static String getRandomIdentity(String s) {

		if (s == null) {

			return "";

		}
		String result = null;

		try {

			byte[] source = s.getBytes("utf-8");
			MessageDigest alg = java.security.MessageDigest.getInstance("MD5");
			alg.update(source);
			byte[] temp = alg.digest();

			char hexDigits[] = {

			'0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c',
					'd', 'e', 'f' };

			char[] str = new char[32];

			for (int i = 0, j = 0; i < 16; i++) {

				byte b = temp[i];

				str[j++] = hexDigits[b >>> 4 & 0xf];

				str[j++] = hexDigits[b & 0xf];

			}

			result = new String(str);

		} catch (NoSuchAlgorithmException ex) {
			
			LOGGER.error(ex.getMessage(), ex);
			
		} catch (UnsupportedEncodingException e) {

			LOGGER.error(e.getMessage(), e);
		}
		return result;
	}

	/**
	 * ��װ��ҳsql
	 * 
	 * */
	public static String sqlForPage(String sqlSource, int start, int limit) {
		if (start > 1) {
			start = start - 1;
		}
		int end = start + limit + 1;
		String sql = "select *  from (select t.*,rownum as rownum_ from("
				+ sqlSource + ")  t where rownum < " + end + ") where rownum_>"
				+ start;
		return sql;

	}

	public static String sqlForCount(String sqlSource) {

		String sql = "select count(*) from (" + sqlSource + ")";
		return sql;
	}

	public static void main(String[] args) {
		Map param = new HashMap();

		System.out.println(SqlUtil.EncoderByMd5("2015/7/15 18:20:00123"));

		System.out.println(SqlUtil.getRandomIdentity("2015/7/15 18:20:00123"));
	}

	public static List ResultSetToBean(ResultSet set, Class clas) {
		SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if (set == null) {
			return new ArrayList();

		}
		List list = new ArrayList();
		Field[] fields = clas.getDeclaredFields();
		try {
			while (set.next()) {
				Object obj = clas.newInstance();
				for (int i = 0; i < fields.length; i++) {
					try {
						Field field = fields[i];
						String key = field.getName();
						Object value = null;
						value = set.getObject(key);
						if (value == null || value.toString().equals("null")) {
							continue;
						}
						if (field.getType() == long.class) {
							Long va = new Long(set.getString(key));
							CommonBeanUtils.setter(obj, key, va);
							continue;
						}
						if (field.getType() == int.class) {
							Integer va = new Integer(set.getString(key));
							CommonBeanUtils.setter(obj, key, va);
							continue;
						}
						if (field.getType() == float.class) {
							Float va = new Float(set.getFloat(key));
							CommonBeanUtils.setter(obj, key, va);
							continue;
						}
						if (field.getType() == Date.class) {
							long va = set.getTimestamp(key).getTime();
							Date temp = new Date(va);
							CommonBeanUtils.setter(obj, key, temp);
							continue;
						}
						if (field.getType() == java.sql.Date.class) {
							Date va = set.getDate(key);
							CommonBeanUtils.setter(obj, key, va);
							continue;
						}
						if (field.getType() == java.sql.Time.class) {
							Date va = set.getTime(key);
							CommonBeanUtils.setter(obj, key, va);
							continue;
						}
						if (field.getType() == java.sql.Timestamp.class) {
							Date va = set.getTimestamp(key);
							CommonBeanUtils.setter(obj, key, va);
							continue;
						}
						if (field.getType() == boolean.class) {

							String s = set.getString(key);
							Boolean temp = Boolean.FALSE;
							if ("1".equals(s)) {

								temp = Boolean.TRUE;
							}
							if ("0".equals(s)) {
								temp = Boolean.FALSE;

							}
							CommonBeanUtils.setter(obj, key, temp);

							continue;
						}

						CommonBeanUtils.setter(obj, key, value);
					} catch (Exception e) {
						
						LOGGER.error(e.getMessage(), e);

						continue;
					}
				}
				list.add(obj);

			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage(), e);
		} catch (InstantiationException e) {
			LOGGER.error(e.getMessage(), e);
		} catch (IllegalAccessException e) {
			LOGGER.error(e.getMessage(), e);
		}

		return list;

	}

}
